package com.sevennotes.classtable.data.database.daos

import androidx.lifecycle.LiveData
import androidx.room.*
import com.sevennotes.classtable.model.ClassItem
import com.sevennotes.classtable.model.ClassItemWithName

@Dao
interface ClassItemDAO {
    @Insert
     fun insertClassItem(classItem: ClassItem)

    @Update
     fun updateClassItem(classItem: ClassItem)

    @Delete
     fun deleteClassItem(classItem: ClassItem)

     @Query("DELETE FROM class_items")
     fun clear()

    @Query("DELETE FROM class_items WHERE student_id = :studentId")
     fun deleteStuAllClasses(studentId: Int)

    @Query("SELECT * FROM class_items")
     fun getAllClassItems(): List<ClassItem>

    @Query("SELECT * FROM class_items WHERE table_id = :tableId")
     fun getAllClassItemsByTableId(tableId: Int): List<ClassItem>

    @Query("SELECT class_items.*, student_items.name AS student_name, student_items.color AS student_color " +
        "FROM class_items JOIN student_items " +
        "ON class_items.student_id = student_items.id " +
        "WHERE table_id = :tableId")
    fun getAllClassItemsWithName(tableId: Int): LiveData<List<ClassItemWithName>>

    @Query("SELECT * FROM class_items WHERE id IS :id")
     fun getClassItemById(id: Int): ClassItem?

    @Query("SELECT * FROM class_items WHERE student_id = :studentId AND table_id = :tableId")
     fun getClassItemByStudentId(studentId: Int, tableId: Int): List<ClassItem>
}